﻿Imports System.IO
Public Class frmuser
    Dim Strsql As String
    Dim Mysql1 As New ClsSQLhelper
    Dim on_load As Boolean = True
    Dim Group_Changed As Boolean = False
    Sub New_DataEntry()
        'TextBox
        Me.TxtUser.Enabled = True
        Me.TxtPassword.Enabled = True
        Me.TxtIdcard.Enabled = True
        Me.TxtFullName.Enabled = True
        Me.TxtPosition.Enabled = True

        Me.TxtUser.Text = ""
        Me.TxtPassword.Text = ""
        Me.TxtFullName.Text = ""
        Me.TxtIdcard.Text = ""
        Me.TxtImgpart.Text = ""


        'Button
        Me.BtnNew.Enabled = True
        Me.BtnDelete.Enabled = True
        Me.BtnSave.Enabled = True

        Me.ChkActive.Checked = False

        Load_Cbosite()
        Load_CboGroup()
        Load_Cbodepartment()
        Me.PictureBox1.Image = WHIS.My.Resources.Resources.NoPix

    End Sub
    Sub Edit_Entry()
        'TextBox
        Me.TxtUser.Enabled = False
        Me.TxtPassword.Enabled = True
        Me.TxtIdcard.Enabled = True
        Me.TxtFullName.Enabled = True
        Me.TxtPosition.Enabled = True
    End Sub
    Function Load_Data(Optional ByVal Str_Keyword As String = "") As Boolean
        Try

            Dim DS As New DataSet

            Load_Data = False
            Strsql = "select user,password,idcard,user_name,position ,department ,user.active from user "
            If Str_Keyword <> "" Then
                Strsql = Strsql & " where user like '" & Str_Keyword & "%'"
            End If
            Strsql = Strsql & " order by active desc ,user_name "

            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            DS = Mysql1.GetMYSQLDataset(Strsql, "DS_user", "user")
            Pn_Framework.Set_GridSetting(Me.gridDetail, DS)

            Me.BtnSave.Enabled = False
            Me.BtnDelete.Enabled = False
            Me.TxtUser.ReadOnly = True
            Load_Data = True
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)
        End Try

    End Function
    Function Validate_Data() As Boolean
        Validate_Data = False
        Try
            If Me.TxtUser.Text.Trim.Length = 0 Then Exit Function
            If Me.TxtPassword.Text.Trim.Length = 0 Then Exit Function
            Validate_Data = True
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    Function Load_Cbosite(Optional ByVal Str_SelectedUser As String = "") As Boolean
        Try
            Dim DS As New DataSet
            Dim lcol As Integer = 0, lrow As Integer = 0
            Load_Cbosite = False



            Strsql = "select * from site"
            DS = Mysql1.GetMYSQLDataset(Strsql, "DS_site", "site")
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            With CboSite
                If DS.Tables(0).Rows.Count = 0 Then Exit Function
                .DataSource = DS.Tables(0)
                .DisplayMember = "site"
                .ValueMember = "siteid"
                .SelectionStart = 0
            End With


            Load_Cbosite = True
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : Function Load_Listview_site - " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)

        End Try
    End Function
    Function Load_CboGroup() As Boolean
        Try
            Dim DS As New DataSet
            Dim lcol As Integer = 0, lrow As Integer = 0
            Load_CboGroup = False

            Strsql = "select * from usergroup"
            If Me.TxtUser.Text <> "" Then
                Strsql = Strsql & " where  usergroupid  not in  ( select usergroup from siteuser where user =  '" & Me.TxtUser.Text & "' and siteid = " & Me.CboSite.SelectedValue & ")"
            End If
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            DS = Mysql1.GetMYSQLDataset(Strsql, "DS_usergroup", "usergroup")
            ' Me.EventLog1.WriteEntry(Strsql, EventLogEntryType.Information)

            With Me.CboGroup
                If DS.Tables(0).Rows.Count = 0 Then Exit Function
                .DataSource = DS.Tables(0)
                .DisplayMember = "usergroup"
                .ValueMember = "usergroupid"
                .SelectionStart = 0
            End With


            Load_CboGroup = True
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : Function Load_Listview_Group - " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)

        End Try
    End Function
    Function Load_Cbodepartment() As Boolean
        Try
            Dim DS As New DataSet
            Dim lcol As Integer = 0, lrow As Integer = 0
            Load_Cbodepartment = False

            Strsql = "select * from department"
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            DS = Mysql1.GetMYSQLDataset(Strsql, "DS_department", "department")
            ' Me.EventLog1.WriteEntry(Strsql, EventLogEntryType.Information)

            With Me.CboDepartment
                If DS.Tables(0).Rows.Count = 0 Then Exit Function
                .DataSource = DS.Tables(0)
                .DisplayMember = "depname"
                .ValueMember = "depcode"
                .SelectionStart = 0
            End With


            Load_Cbodepartment = True
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : Function Load_Listview_Group - " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)

        End Try
    End Function
    Private Sub frmsection_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Load_Data() = False Then
            GoTo Load_Data_Fails
        End If
        Load_Cbosite()
        Load_CboGroup()
        Load_Cbodepartment()

        on_load = False
        Exit Sub
Load_Data_Fails:
        MsgBox("ระบบพบว่าฐานข้อมูลของคุณไม่ update หรือ ไม่ถูกต้อง", MsgBoxStyle.Exclamation, "กรุณาตรวจสอบฐานข้อมูลของคุณ")
    End Sub

    Private Sub gridsection_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        Try
            With gridDetail

                If .RowCount = 0 Then Exit Sub
                Me.TxtUser.ReadOnly = True
                Me.TxtUser.Text = .Item(0, e.RowIndex).Value.ToString
                Me.TxtPassword.Text = Crypto.Decrypt(.Item(1, e.RowIndex).Value.ToString, "sys11266")
                Me.TxtIdcard.Text = .Item(2, e.RowIndex).Value.ToString
                Me.TxtFullName.Text = .Item(3, e.RowIndex).Value.ToString
                Me.TxtPosition.Text = .Item(4, e.RowIndex).Value.ToString
                Me.CboDepartment.SelectedValue = .Item(5, e.RowIndex).Value.ToString
                Me.ChkActive.Checked = .Item(6, e.RowIndex).Value.ToString
                Me.BtnSave.Enabled = True
                Me.BtnDelete.Enabled = True
                Strsql = "select * from user where user = '" & Me.TxtUser.Text & "'"
                Dim DS As New DataSet
                DS = Mysql1.GetMYSQLDataset(Strsql, "DS_User", "USER")
                Dim imgByteArray() As Byte
                Me.PictureBox1.Image = WHIS.My.Resources.Resources.NoPix
                If IsDBNull(DS.Tables(0).Rows(0).Item("picture")) <> True Then
                    imgByteArray = CType(DS.Tables(0).Rows(0).Item("picture"), Byte())
                    Dim MyStream As New System.IO.MemoryStream(imgByteArray)
                    Me.PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                    Me.PictureBox1.Image = Image.FromStream(MyStream)
                    MyStream.Close()

                End If

                Strsql = "select * from siteuser where user = '" & Me.TxtUser.Text & "'"
                DS = Mysql1.GetMYSQLDataset(Strsql, "DS_usergroup", "usergroup")
                Me.CboSite.SelectedValue = DS.Tables(0).Rows(0).Item("siteid")
            End With
            With Me.TabControl1
                .SelectedTab = TabPage2
            End With

            Me.BtnDelete.Enabled = True
            Me.BtnSave.Enabled = True
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            If MsgBox("คุณต้องการลบข้อมูลของ " & Me.TxtUser.Text & " หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยันการลบ") = MsgBoxResult.No Then Exit Sub

            Dim Strsql As String
            Dim Mysql1 As New ClsSQLhelper
            'Delete user from table user
            Strsql = "Delete from user where user = '" & Me.TxtUser.Text & "'"
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            Mysql1.MySQLExecute(Strsql)

            'Delete user from table siteuser
            Strsql = "Delete from siteuser where user = '" & Me.TxtUser.Text & "' and siteid = " & My.Settings.CurrentSite
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            Mysql1.MySQLExecute(Strsql)

            'Refresh Data
            Load_Data()
            New_DataEntry()
            Me.TxtUser.ReadOnly = True
            Me.TabControl1.SelectedTab = TabPage1
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)
        End Try
    End Sub


    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            If Validate_Data() = False Then
                MsgBox("คุณกรอกข้อมูลไม่ครบ", MsgBoxStyle.Exclamation, "ไม่สามารถบันทึกได้")
                Exit Sub
            End If

            If MsgBox("คุณต้องการบันทึกข้อมูลหรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยันการบันทึก") = MsgBoxResult.No Then Exit Sub
            Me.TxtUser.ReadOnly = True
            Dim Strsql As String
            Dim Mysql1 As New ClsSQLhelper
            Dim DS As New DataSet
            Strsql = "Select * from user where user= '" & Me.TxtUser.Text & "'"
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            DS = Mysql1.GetMYSQLDataset(Strsql, "DS_user", "user")
            If DS.Tables(0).Rows.Count = 0 Then
                'Insert into table user
                Strsql = "Insert into user (user,password,user_name,idcard,position,department,picture) values ('" & Me.TxtUser.Text & "','" & Crypto.Encrypt(Me.TxtPassword.Text, "11266") & "', '" & Me.TxtFullName.Text & "','" & Me.TxtIdcard.Text & "','" & Me.TxtPosition.Text & "', " & Me.CboDepartment.SelectedValue & ",LOAD_FILE('" & Replace(Me.TxtImgpart.Text, "\", "\\") & "')"
                Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                Mysql1.MySQLExecute(Strsql)

                Strsql = "Insert into siteuser (user,siteid,usergroup) values  ('" & Me.TxtUser.Text & "'," & Me.CboSite.SelectedValue & "," & Me.CboGroup.SelectedValue & ")"
                Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                Mysql1.MySQLExecute(Strsql)


                MsgBox("เพิ่มข้อมูลเข้าเรียบร้อยแล้ว", MsgBoxStyle.Information)
            Else
                'Update table user
                Strsql = "update user set password = '" & Crypto.Encrypt(Me.TxtPassword.Text, "11266") & "', idcard = '" & Me.TxtIdcard.Text & "',user_name = '" & Me.TxtFullName.Text & "',active = " & Me.ChkActive.Checked & " ,position =  '" & Me.TxtPosition.Text & "' ,department = " & Me.CboDepartment.SelectedValue & " ,picture = LOAD_FILE('" & Replace(Me.TxtImgpart.Text, "\", "\\") & "')"
                Strsql = Strsql & " where user = '" & Me.TxtUser.Text.Trim & "'"
                Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                Mysql1.MySQLExecute(Strsql)

                Strsql = "update siteuser set usergroup = " & Me.CboGroup.SelectedValue & " and  siteid = " & Me.CboSite.SelectedValue & " where user = '" & Me.TxtUser.Text & "'"
                Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                Mysql1.MySQLExecute(Strsql)
                MsgBox("ปรับปรุงข้อมูลเข้าเรียบร้อยแล้ว", MsgBoxStyle.Information)
            End If
            'Refresh Data
            Load_Data()

            New_DataEntry()
            Me.TxtUser.ReadOnly = True
            Me.TabControl1.SelectedTab = TabPage1
        Catch ex As Exception
            Me.EventLog1.WriteEntry(Me.Name & " : " & ex.Message, EventLogEntryType.Error, 1)
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub BtnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        New_DataEntry()


    End Sub
    Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
        Me.Dispose()
    End Sub

    Private Sub LVGroupName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If on_load = True Then Exit Sub
    End Sub

    Private Sub gridDetail_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)

    End Sub

    Private Sub PicLoadImg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            With Me.OpenFileDialog1
                .Title = "เลือกรูปภาพ"
                .FileName = ""
                .Filter = "JPG Image|*.jpg"
                .ShowDialog()

                Me.TxtImgpart.Text = .FileName
                Dim bm As New Bitmap(Me.TxtImgpart.Text)
                PictureBox1.Image = bm
            End With
        Catch ex As Exception

        End Try
    End Sub

    Private Sub PictureBox6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Frmcapture.ShowDialog()
        Me.TxtImgpart.Text = Frmcapture.MyCapturepart
        Frmcapture.Dispose()
    End Sub

    Private Sub picZoomin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        FrmFullSize.PictureBox1.Image = Me.PictureBox1.Image
        FrmFullSize.Show()

    End Sub

    Private Sub TxtImgpart_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Dim bm As New Bitmap(Me.TxtImgpart.Text.Trim)
            PictureBox1.Image = bm
            PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage

        Catch ex As Exception
            ' MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub TxtSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TxtSearch.KeyDown
        If e.KeyCode = Keys.Enter Then
            Load_Data(Me.TxtSearch.Text)
        End If
    End Sub
End Class